package com.os.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

import com.os.dao.RequestDaoM;
import com.os.util.JdbcConUtilM;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
/**
 * 
 * @author MWX
 *
 */
public class RequestDaoImplM implements RequestDaoM{

	@Override
	public JSONArray findAllRequests(int page,int rows) {
		// 获取诉求信息
		JSONArray ja=new JSONArray();
		StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
		sql.append(" from o_user");
		sql.append(" INNER JOIN o_request");
		sql.append(" on o_request.uid=o_user.uid");
		sql.append(" INNER JOIN o_food");
		sql.append(" on o_request.fid=o_food.fid");
		sql.append(" limit ?,?");
		Connection con=JdbcConUtilM.getCon();
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("rid", rs.getInt("rid"));
				j.put("oid", rs.getInt("oid"));
				j.put("fname", rs.getString("fname"));
				if(rs.getInt("rcontent")==-1){
					j.put("rcontent", "申请退菜");
				}else if(rs.getInt("rcontent")==0){
					j.put("rcontent", "申请换菜");
				}else{
					j.put("rcontent", "申请错误");
				}
				j.put("rcount", rs.getInt("rcount"));
				if(rs.getInt("rstate")==0){
					j.put("rstate", "审批未通过");
				}else if(rs.getInt("rstate")==1){
					j.put("rstate", "审批已通过");
				}else{
					j.put("rstate", "未审批");
				}
				j.put("uname", rs.getString("uname"));
				j.put("rtime", rs.getString("rtime"));
				ja.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return ja;
	}

	@Override
	public JSONArray checkRequests(int page, int rows) {
		// 获取所有未审批诉求信息
		JSONArray ja=new JSONArray();
		StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
		sql.append(" from o_user");
		sql.append(" INNER JOIN o_request");
		sql.append(" on o_request.uid=o_user.uid");
		sql.append(" INNER JOIN o_food");
		sql.append(" on o_request.fid=o_food.fid");
		sql.append(" where o_request.rstate=-1");
		sql.append(" limit ?,?");
		Connection con=JdbcConUtilM.getCon();
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, (page-1)*rows);
			pst.setInt(2, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("rid", rs.getInt("rid"));
				j.put("oid", rs.getInt("oid"));
				j.put("fname", rs.getString("fname"));
				if(rs.getInt("rcontent")==-1){
					j.put("rcontent", "申请退菜");
				}else if(rs.getInt("rcontent")==0){
					j.put("rcontent", "申请换菜");
				}else{
					j.put("rcontent", "申请错误");
				}
				j.put("rcount", rs.getInt("rcount"));
				j.put("rstate", "未审批");
				j.put("uname", rs.getString("uname"));
				j.put("rtime", rs.getString("rtime"));
				ja.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return ja;
	}

	@Override
	public String updaterstate(int rid, String state) {
		String tempdate=null;
		PreparedStatement pst=null;
		PreparedStatement pst1=null;
		PreparedStatement pst2=null;
		PreparedStatement pst3=null;
		PreparedStatement pst4=null;
		PreparedStatement pst5=null;
		Connection con=JdbcConUtilM.getCon();
		//审批诉求
		StringBuffer sql=new StringBuffer("update o_request ");
		sql.append(" set o_request.rstate=?");
		sql.append(" where o_request.rstate=-1");
		sql.append(" and o_request.rid =?");
		try {
			pst=con.prepareStatement(sql.toString());
			if(state.equals("审批未通过")){
				pst.setInt(1, 0);
			}else if(state.equals("审批通过")){
				pst.setInt(1, 1);
				//查询库存
				 int fnums=0;
				 int fid=0;
				 int oid=0;
				 String foodlist="";
				 StringBuffer sql2=new StringBuffer("select o_store.*,o_request.oid from o_store");
				 sql2.append(" INNER JOIN o_request");
				 sql2.append(" on o_store.fid=o_request.fid");
				 sql2.append(" where o_request.rid=?");
				 pst2=con.prepareStatement(sql2.toString());
				 pst2.setInt(1, rid);
				 ResultSet rs=pst2.executeQuery();
				 if(rs.next()){
					 fnums=rs.getInt("fnums");
					 fid=rs.getInt("fid");
					 oid=rs.getInt("oid");
				 }
				 //System.out.println(fnums);
				//增加库存
				 StringBuffer sql3=new StringBuffer("update o_store");
				 sql3.append(" SET o_store.fnums=?");
				 sql3.append(" where o_store.fid=?");
				 pst3=con.prepareStatement(sql3.toString());
				 pst3.setInt(1, fnums+1);
				 pst3.setInt(2, fid);
				 int sadd=pst3.executeUpdate();
				// System.out.println(sadd);
				//查询菜单
				 StringBuffer sql4=new StringBuffer("select o_desk.foodlist");
				 sql4.append(" from o_desk");
				 sql4.append(" where o_desk.oid=?");
				 pst4=con.prepareStatement(sql4.toString());
				 pst4.setInt(1, oid);
				 ResultSet rs4=pst4.executeQuery();
				 if(rs4.next()){
					 foodlist=rs4.getString("foodlist");
				 }
				 String[] fl=foodlist.split(",");
				 StringBuffer nfoodlist=new StringBuffer();
				 for (String str : fl) {
					if(!str.equals(String.valueOf(fid))){
						nfoodlist.append(str).append(",");
					}
				}
				 String newfoodlist=nfoodlist.toString().substring(0, nfoodlist.toString().length()-1);
				 System.out.println(newfoodlist);
				 //修改菜单
				 StringBuffer sql5=new StringBuffer("update o_desk");
				 sql5.append(" set o_desk.foodlist=?");
				 sql5.append(" where o_desk.oid=?");
				 pst5=con.prepareStatement(sql5.toString());
				 pst5.setString(1, newfoodlist);
				 pst5.setInt(2, oid);
				int coid= pst5.executeUpdate();
				//System.out.println(coid);
			}
			pst.setInt(2, rid);
			int result=pst.executeUpdate();
			//System.out.println(result);
			if(result!=0){
				
				//更新修改时间
				Date d=new Date();
				long s=d.getTime();
				Date d1=new Date(s);
				DateFormat df=new SimpleDateFormat("yyyy年MM月dd日 HH:mm");
				String sdate=df.format(d1);
				StringBuffer sql1=new StringBuffer("update o_request");
				sql1.append(" set o_request.rtime=?");
				sql1.append(" where  o_request.rid=?");
				
				pst1=con.prepareStatement(sql1.toString());
				pst1.setString(1, sdate);
				pst1.setInt(2, rid);
				int result1=pst1.executeUpdate();
				//System.out.println(result1);
				if(result1!=0){
					tempdate=sdate;
				}
				JdbcConUtilM.closePstAndCon(pst1, con);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return tempdate;
	}

	@Override
	public int getUncheckRequests() {
		// TODO Auto-generated method stub
		int count =0;
		StringBuffer sql=new StringBuffer("select COUNT(o_request.rid) as counts");
		sql.append(" from o_request");
		sql.append(" where o_request.rstate=-1");
		PreparedStatement pst=null;
		Connection con=JdbcConUtilM.getCon();
		try {
			pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				count=rs.getInt("counts");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return count;
	}

	@Override
	public int getAllRequests() {
		// TODO Auto-generated method stub
		int count =0;
		StringBuffer sql=new StringBuffer("select COUNT(o_request.rid) as counts");
		sql.append(" from o_request");
		PreparedStatement pst=null;
		Connection con=JdbcConUtilM.getCon();
		try {
			pst=con.prepareStatement(sql.toString());
			ResultSet rs=pst.executeQuery();
			if(rs.next()){
				count=rs.getInt("counts");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		return count;
	}

	@Override
	public JSONArray findRequestsByFname(String fname) {
		// TODO Auto-generated method stub
		JSONArray ja=new JSONArray();
		//根据菜名模糊搜索
		StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
		sql.append(" from o_user");
		sql.append(" INNER JOIN o_request");
		sql.append(" on o_request.uid=o_user.uid");
		sql.append(" INNER JOIN o_food");
		sql.append(" on o_request.fid=o_food.fid");
		sql.append(" where o_food.fname like ?");
		
		
		//创建连接
		Connection con=JdbcConUtilM.getCon();
		//获取预处理对象
		PreparedStatement pst=null;
		
		try {
			 pst=con.prepareStatement(sql.toString());
			 pst.setString(1, "%"+fname+"%");
			 ResultSet rs=pst.executeQuery();
			 while(rs.next()){
				 JSONObject j=new JSONObject();
					j.put("rid", rs.getInt("rid"));
					j.put("oid", rs.getInt("oid"));
					j.put("fname", rs.getString("fname"));
					if(rs.getInt("rcontent")==-1){
						j.put("rcontent", "申请退菜");
					}else if(rs.getInt("rcontent")==0){
						j.put("rcontent", "申请换菜");
					}else{
						j.put("rcontent", "申请错误");
					}
					j.put("rcount", rs.getInt("rcount"));
					if(rs.getInt("rstate")==0){
						j.put("rstate", "审批未通过");
					}else if(rs.getInt("rstate")==1){
						j.put("rstate", "审批已通过");
					}else{
						j.put("rstate", "未审批");
					}
					j.put("uname", rs.getString("uname"));
					j.put("rtime", rs.getString("rtime"));
					ja.add(j);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		
		return ja;
	}

	@Override
	public JSONArray findChecksByDate(String year, String month, String day) {
		JSONArray ja=new JSONArray();
		StringBuffer s=new StringBuffer();
		if(month.length()==1){
			if(day.length()==1){
			s.append(year).append("年").append("0").append(month).append("月").append("0").append(day).append("日");
			}else{
			s.append(year).append("年").append("0").append(month).append("月").append(day).append("日");	
			}
		}else{
			if(day.length()==1){
			s.append(year).append("年").append(month).append("月").append("0").append(day).append("日");
			}else{
			s.append(year).append("年").append(month).append("月").append(day).append("日");	
			}
		}
		//根据日期搜索
		StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
		sql.append(" from o_user");
		sql.append(" INNER JOIN o_request");
		sql.append(" on o_request.uid=o_user.uid");
		sql.append(" INNER JOIN o_food");
		sql.append(" on o_request.fid=o_food.fid");
		sql.append(" where o_request.rtime like ?");
		//创建连接
		Connection con=JdbcConUtilM.getCon();
		//获取预处理对象
		PreparedStatement pst=null;
		try {
			 pst=con.prepareStatement(sql.toString());
			 pst.setString(1, s.toString()+"%");
			
			 ResultSet rs=pst.executeQuery();
			 while(rs.next()){
				 JSONObject j=new JSONObject();
					j.put("rid", rs.getInt("rid"));
					j.put("oid", rs.getInt("oid"));
					j.put("fname", rs.getString("fname"));
					if(rs.getInt("rcontent")==-1){
						j.put("rcontent", "申请退菜");
					}else if(rs.getInt("rcontent")==0){
						j.put("rcontent", "申请换菜");
					}else{
						j.put("rcontent", "申请错误");
					}
					j.put("rcount", rs.getInt("rcount"));
					if(rs.getInt("rstate")==0){
						j.put("rstate", "审批未通过");
					}else if(rs.getInt("rstate")==1){
						j.put("rstate", "审批已通过");
					}else{
						j.put("rstate", "未审批");
					}
					j.put("uname", rs.getString("uname"));
					j.put("rtime", rs.getString("rtime"));
					ja.add(j);
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst, con);
		
		return ja;
	}

	@Override
	public JSONObject findChecksByUid(int page, int rows, int uid) {
		Connection con=JdbcConUtilM.getCon();
		// 获取当前服务员下的诉求总数
		int total=0;
		StringBuffer sql2=new StringBuffer("select count(o_request.rid) as nums");
		sql2.append(" from o_request");
		sql2.append(" where o_request.r_rid=?");
		PreparedStatement pst2=null;
		try {
			pst2=con.prepareStatement(sql2.toString());
			pst2.setInt(1, uid);
			ResultSet rs2=pst2.executeQuery();
			if(rs2.next()){
			total=rs2.getInt("nums");
			}
		} catch (SQLException e2) {
			// TODO Auto-generated catch block
			e2.printStackTrace();
		}
		//根据用户id获取用户名
		String userName="";
		StringBuffer sql1=new StringBuffer("select uname from o_user");
		sql1.append(" where uid=?");
		PreparedStatement pst1=null;
		try {
			pst1=con.prepareStatement(sql1.toString());
			pst1.setInt(1, uid);
			ResultSet rs1=pst1.executeQuery();
			if(rs1.next()){
				userName=rs1.getString("uname");
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		//查看当前点菜员下的诉求审批状态
		JSONArray ja=new JSONArray();
		StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
		sql.append(" from o_user");
		sql.append(" INNER JOIN o_request");
		sql.append(" on o_request.uid=o_user.uid");
		sql.append(" INNER JOIN o_food");
		sql.append(" on o_request.fid=o_food.fid");
		sql.append(" where o_request.r_rid=?");
		sql.append(" limit ?,?");
		
		PreparedStatement pst=null;
		try {
			pst=con.prepareStatement(sql.toString());
			pst.setInt(1, uid);
			pst.setInt(2, (page-1)*rows);
			pst.setInt(3, rows);
			ResultSet rs=pst.executeQuery();
			while(rs.next()){
				JSONObject j=new JSONObject();
				j.put("rid", rs.getInt("rid"));
				j.put("oid", rs.getInt("oid"));
				j.put("r_rid",userName);
				j.put("fname", rs.getString("fname"));
				if(rs.getInt("rcontent")==-1){
					j.put("rcontent", "申请退菜");
				}else if(rs.getInt("rcontent")==0){
					j.put("rcontent", "申请换菜");
				}else{
					j.put("rcontent", "申请错误");
				}
				j.put("rcount", rs.getInt("rcount"));
				if(rs.getInt("rstate")==0){
					j.put("rstate", "审批未通过");
				}else if(rs.getInt("rstate")==1){
					j.put("rstate", "审批已通过");
				}else{
					j.put("rstate", "未审批");
				}
				j.put("uname", rs.getString("uname"));
				j.put("rtime", rs.getString("rtime"));
				ja.add(j);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		JdbcConUtilM.closePstAndCon(pst2, con);
		JdbcConUtilM.closePstAndCon(pst1, con);
		JdbcConUtilM.closePstAndCon(pst, con);
		JSONObject jj=new JSONObject();
		jj.put("total", total);
		jj.put("rows", ja);
		return jj;
	}

	@Override
	public JSONArray findChecksByFnameUid(String fname, int uid) {
		// TODO Auto-generated method stub
		JSONArray ja=new JSONArray();
		Connection con=JdbcConUtilM.getCon();
		//根据uid获取用户名
		String userName="";
		StringBuffer sql1=new StringBuffer("select uname from o_user");
		sql1.append(" where uid=?");
		PreparedStatement pst1=null;
		try {
			pst1=con.prepareStatement(sql1.toString());
			pst1.setInt(1, uid);
			ResultSet rs1=pst1.executeQuery();
			if(rs1.next()){
				userName=rs1.getString("uname");
			}
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		
		//根据菜名模糊搜索查看当前点菜员下的诉求审批状态
				StringBuffer sql=new StringBuffer("select o_request.*,o_food.fname,o_user.uname");
				sql.append(" from o_user");
				sql.append(" INNER JOIN o_request");
				sql.append(" on o_request.uid=o_user.uid");
				sql.append(" INNER JOIN o_food");
				sql.append(" on o_request.fid=o_food.fid");
				sql.append(" where o_food.fname like ?");
				sql.append(" and o_request.r_rid=?");
				//获取预处理对象
				PreparedStatement pst=null;
				
				try {
					 pst=con.prepareStatement(sql.toString());
					 pst.setString(1, "%"+fname+"%");
					 pst.setInt(2, uid);
					 ResultSet rs=pst.executeQuery();
					 while(rs.next()){
						 JSONObject j=new JSONObject();
							j.put("rid", rs.getInt("rid"));
							j.put("oid", rs.getInt("oid"));
							j.put("r_rid",userName);
							j.put("fname", rs.getString("fname"));
							if(rs.getInt("rcontent")==-1){
								j.put("rcontent", "申请退菜");
							}else if(rs.getInt("rcontent")==0){
								j.put("rcontent", "申请换菜");
							}else{
								j.put("rcontent", "申请错误");
							}
							j.put("rcount", rs.getInt("rcount"));
							if(rs.getInt("rstate")==0){
								j.put("rstate", "审批未通过");
							}else if(rs.getInt("rstate")==1){
								j.put("rstate", "审批已通过");
							}else{
								j.put("rstate", "未审批");
							}
							j.put("uname", rs.getString("uname"));
							j.put("rtime", rs.getString("rtime"));
							ja.add(j);
					 }
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				JdbcConUtilM.closePstAndCon(pst, con);
				
				return ja;
	}

	

}
